In [1]:
import pandas as pd
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
import geopandas as gpd
import psycopg2
from psycopg2 import OperationalError
In [2]:
def create_connection():
try:
connection = psycopg2.connect(
dbname='graduates_db', # База данных
user='postgres', # Имя пользователя PostgreSQL
password='12345678', # Пароль
host='postgres', # Адрес PostgreSQL
port='5432'
)
if connection.closed == 0:
print("Connected to PostgreSQL database.")
return connection
except OperationalError as e:
print("Error while connecting to PostgreSQL")
print(f"Error: '{e}'")
return None
In [3]:
def fetch_data(query):
engine = create_connection()
df = pd.read_sql(query, con=engine)
return df
In [4]:
# Средняя зп по регионам
def plot_salary_by_region_and_education():
query = """
SELECT r.object_name AS region,
g.education_level,
AVG(g.average_salary) AS avg_salary,
SUM(g.count_graduate * g.percent_employed / 100) AS total_employed
FROM Graduates g
JOIN Regions r ON g.region_id = r.region_id
WHERE g.average_salary IS NOT NULL AND r.object_name != 'Российская Федерация' AND r.object_name NOT LIKE '%федеральный округ'
GROUP BY r.object_name, g.education_level
ORDER BY region;
"""
df = fetch_data(query)
if df is not None:
education_levels = ['Бакалавриат, специалитет', 'Магистратура', 'СПО: специалисты среднего звена', 'СПО: квалифицированные рабочие и служащие']
# Отфильтруем регионы по средней зарплате магистратуры и отсортируем
magister_df = df[df['education_level'] == 'Магистратура'].sort_values(by='avg_salary', ascending=False)
sorted_regions = magister_df['region'].values
# Подготовим словарь регионов с количеством трудоустроенных для отображения
total_employed_by_region = df.groupby('region')['total_employed'].sum().to_dict()
# Обновим названия регионов с добавлением количества трудоустроенных
labeled_regions = [
f"{region} ({int(total_employed_by_region[region])} чел.)" if region in total_employed_by_region else region
for region in sorted_regions
]
fig, ax = plt.subplots(figsize=(18, 8))
# Построение линии для каждого уровня образования
for level in education_levels:
df_level = df[df['education_level'] == level].set_index('region').reindex(sorted_regions)
avg_salary = df_level['avg_salary']
ax.plot(labeled_regions, avg_salary, marker='o', label=level)
ax.set_xlabel('Регион')
ax.set_ylabel('Средняя зарплата')
ax.set_title('Средняя зарплата выпускников по регионам и уровням образования')
ax.legend(title="Уровень образования")
ax.tick_params(axis='x', rotation=90)
plt.xticks(rotation=45, ha='right')
plt.grid()
plt.tight_layout()
plt.show()
plot_salary_by_region_and_education()
In [5]:
def plot_employment_rate_with_counts():
query = """
SELECT
r.object_name AS region,
g.education_level,
AVG(g.percent_employed) AS avg_employment_rate,
SUM(g.count_graduate * g.percent_employed / 100) AS total_employed
FROM Graduates g
JOIN Regions r ON g.region_id = r.region_id
WHERE g.percent_employed IS NOT NULL AND g.count_graduate IS NOT NULL
AND r.object_name != 'Российская Федерация' AND r.object_name NOT LIKE '%федеральный округ'
GROUP BY r.object_name, g.education_level
ORDER BY region;
"""
df = fetch_data(query)
if df is not None:
education_levels = ['Бакалавриат, специалитет', 'Магистратура', 'СПО: специалисты среднего звена', 'СПО: квалифицированные рабочие и служащие']
# Отфильтруем регионы по проценту трудоустроенных магистратуры и отсортируем
magister_df = df[df['education_level'] == 'Магистратура'].sort_values(by='avg_employment_rate', ascending=False)
sorted_regions = magister_df['region'].values
# Подготовим словарь регионов с количеством трудоустроенных для отображения
total_employed_by_region = df.groupby('region')['total_employed'].sum().to_dict()
# Обновим названия регионов с добавлением количества трудоустроенных
labeled_regions = [
f"{region} ({int(total_employed_by_region[region])} чел.)" if region in total_employed_by_region else region
for region in sorted_regions
]
fig, ax = plt.subplots(figsize=(18, 8))
# Построение линии для каждого уровня образования
for level in education_levels:
df_level = df[df['education_level'] == level].set_index('region').reindex(sorted_regions)
avg_employment_rate = df_level['avg_employment_rate']
ax.plot(labeled_regions, avg_employment_rate, marker='o', label=level)
ax.set_xlabel('Регион (количество трудоустроенных)')
ax.set_ylabel('Средний процент трудоустроенных (%)')
ax.set_title('Средний процент трудоустроенных выпускников по регионам и уровням образования')
ax.legend(title="Уровень образования")
ax.tick_params(axis='x', rotation=90)
plt.xticks(rotation=45, ha='right')
plt.grid()
plt.tight_layout()
plt.show()
plot_employment_rate_with_counts()
In [6]:
def plot_graduates_by_year_education():
query = """
SELECT g.year, g.education_level, SUM(g.count_graduate) AS total_graduates
FROM Graduates g
WHERE g.year BETWEEN 2019 AND 2023
GROUP BY g.year, g.education_level
ORDER BY g.year;
"""
df = fetch_data(query)
if df is not None:
# Определение уровней образования и установка параметров для 2x2 сетки графиков
education_levels = ["Бакалавриат, специалитет", "Магистратура", "СПО: специалисты среднего звена", "СПО: квалифицированные рабочие и служащие"]
fig, axes = plt.subplots(2, 2, figsize=(14, 10))
# Находим максимальное значение для общей настройки y-оси
max_value = df['total_graduates'].max()
for idx, (level, ax) in enumerate(zip(education_levels, axes.flatten())):
df_level = df[df['education_level'] == level]
# Фильтрация для значений за каждый год
df_level = df_level[df_level['year'].isin([2019, 2020, 2021, 2022, 2023])]
# Построение столбчатой диаграммы
ax.bar(df_level['year'], df_level['total_graduates'], color='red')
ax.set_title(f"Количество выпускников {level}")
ax.set_xlabel('Год')
ax.set_ylabel('Количество выпускников')
ax.set_xticks([2019, 2020, 2021, 2022, 2023])
ax.set_ylim(0, max_value) # Установка общего масштаба y-оси
plt.tight_layout()
plt.show()
plot_graduates_by_year_education()
In [7]:
from shapely.geometry import Polygon, MultiPolygon
def shift_longitude(geometry):
"""
Функция для преобразования отрицательных долгот в положительные, чтобы устранить разрыв карты.
"""
if geometry.bounds[0] < -150: # Проверяем, есть ли отрицательные координаты долготы
new_coords = []
for polygon in geometry.geoms:
shifted_coords = [
[(x + 360 if x < 0 else x), y] for x, y in polygon.exterior.coords
]
new_coords.append(Polygon(shifted_coords))
return MultiPolygon(new_coords)
return geometry
In [8]:
#Общее количество выпускников магистратуры по регионам
def plot_graduates_heatmap():
# Загрузка данных о выпускниках
query = """
SELECT r.object_name AS region, SUM(g.count_graduate) AS total_graduates
FROM Graduates g
JOIN Regions r ON g.region_id = r.region_id
WHERE g.education_level = 'Магистратура' AND r.object_name != 'Российская Федерация' AND r.object_name NOT LIKE '%федеральный округ'
GROUP BY region
ORDER BY SUM(g.count_graduate);
"""
graduates_df = fetch_data(query)
# Загрузка данных о географии России
russia_geo = gpd.read_file("russia_regions/russia_regions.geojson")
# Объединение данных о выпускниках с географическими данными
russia_geo = russia_geo.merge(graduates_df, left_on="region", right_on="region", how="left")
russia_geo["total_graduates"] = russia_geo["total_graduates"].fillna(0)
print(russia_geo[["region", "total_graduates"]].sort_values(by="total_graduates", ascending=False).head(10))
# Преобразование долгот для правильного отображения на карте
russia_geo["geometry"] = russia_geo["geometry"].apply(shift_longitude)
# Найти третье по величине значение
top_values = russia_geo["total_graduates"].nlargest(3)
third_max_graduates = top_values.iloc[2] # Третье по величине значение
# Построение тепловой карты
fig, ax = plt.subplots(1, 1, figsize=(20, 10))
russia_geo.plot(
column="total_graduates",
cmap="GnBu",
linewidth=0.1,
ax=ax,
edgecolor="0.8",
legend=True,
vmin=0,
vmax=third_max_graduates
)
# Добавление уникальных цветов для Москвы и Санкт-Петербурга
for idx, row in russia_geo.iterrows():
centroid = row["geometry"].centroid
if row["region"] == "Москва" or row["region"] == "Санкт-Петербург":
ax.text(centroid.x, centroid.y, int(row["total_graduates"]),
ha="center", va="center", fontsize=7, color="black", weight="bold")
ax.set_title("Общее количество выпускников магистратуры по регионам")
ax.axis("off") # Убираем оси для чистоты визуализации
plt.show()
plot_graduates_heatmap()
In [9]:
#Общее количество выпускников бакалавриата, специалитета по регионам
def plot_graduates_heatmap_bachelor():
# Загрузка данных о выпускниках
query = """
SELECT r.object_name AS region, SUM(g.count_graduate) AS total_graduates
FROM Graduates g
JOIN Regions r ON g.region_id = r.region_id
WHERE g.education_level = 'Бакалавриат, специалитет' AND r.object_name != 'Российская Федерация' AND r.object_name NOT LIKE '%федеральный округ'
GROUP BY region
ORDER BY SUM(g.count_graduate);
"""
graduates_df = fetch_data(query)
# Загрузка данных о географии России
russia_geo = gpd.read_file("russia_regions/russia_regions.geojson")
# Объединение данных о выпускниках с географическими данными
russia_geo = russia_geo.merge(graduates_df, left_on="region", right_on="region", how="left")
russia_geo["total_graduates"] = russia_geo["total_graduates"].fillna(0)
print(russia_geo[["region", "total_graduates"]].sort_values(by="total_graduates", ascending=False).head(10))
# Преобразование долгот для правильного отображения на карте
russia_geo["geometry"] = russia_geo["geometry"].apply(shift_longitude)
# Найти третье по величине значение
top_values = russia_geo["total_graduates"].nlargest(3)
third_max_graduates = top_values.iloc[2] # Третье по величине значение
# Построение тепловой карты
fig, ax = plt.subplots(1, 1, figsize=(20, 10))
russia_geo.plot(
column="total_graduates",
cmap="GnBu",
linewidth=0.1,
ax=ax,
edgecolor="0.8",
legend=True,
vmin=0,
vmax=third_max_graduates
)
# Добавление уникальных цветов для Москвы и Санкт-Петербурга
for idx, row in russia_geo.iterrows():
centroid = row["geometry"].centroid
if row["region"] == "Москва" or row["region"] == "Санкт-Петербург":
ax.text(centroid.x, centroid.y, int(row["total_graduates"]),
ha="center", va="center", fontsize=7, color="black", weight="bold")
ax.set_title("Общее количество выпускников бакалавриата, специалитета по регионам")
ax.axis("off") # Убираем оси для чистоты визуализации
plt.show()
plot_graduates_heatmap_bachelor()
In [10]:
def plot_mean_employment_by_study_area():
query = """
SELECT sa.study_area_name AS study_area, g.percent_employed, g.count_graduate
FROM Graduates g
JOIN Study_Areas sa ON g.study_area_id = sa.study_area_id;
"""
df = fetch_data(query)
if df is not None:
# Рассчитываем средний процент трудоустроенных и общее количество выпускников по областям
mean_employment = df.groupby('study_area').agg(
percent_employed=('percent_employed', 'mean'),
total_graduates=('count_graduate', 'sum')
).reset_index()
mean_employment = mean_employment.sort_values(by='percent_employed', ascending=False)
# Подписи с количеством выпускников
mean_employment['study_area_label'] = mean_employment.apply(
lambda row: f"{row['study_area']} ({row['total_graduates']:,} чел.)".replace(',', ' '), axis=1
)
# Построение графика
plt.figure(figsize=(16, 10))
plt.plot(mean_employment['study_area_label'], mean_employment['percent_employed'], marker='o', linestyle='-', color='r')
plt.xlabel('Область образования (Количество выпускников)')
plt.ylabel('Процент трудоустроенных')
plt.title('Процент трудоустройства по областям образования')
plt.xticks(rotation=45, ha='right')
plt.ylim(0, mean_employment['percent_employed'].max() + 5)
# Добавление значений процентов над каждой точкой
for i, percent in enumerate(mean_employment['percent_employed']):
plt.text(i, percent + 3, f'{percent:.1f}', ha='center', va='bottom', fontsize=12, color='black')
plt.tight_layout()
plt.grid()
plt.show()
plot_mean_employment_by_study_area()
In [11]:
def plot_mean_employment_by_specialty_section():
query = """
SELECT s.specialty_section AS specialty_section, g.percent_employed, g.count_graduate
FROM Graduates g
JOIN Specialties s ON g.specialty_id = s.specialty_id;
"""
df = fetch_data(query)
if df is not None:
# Рассчитываем средний процент трудоустроенных и общее количество выпускников по укрупненным группам специальностей
mean_employment = df.groupby('specialty_section').agg(
percent_employed=('percent_employed', 'mean'),
total_graduates=('count_graduate', 'sum')
).reset_index()
mean_employment = mean_employment.sort_values(by='percent_employed', ascending=False)
# Формируем метки для укрупненных групп с форматированием количества выпускников
mean_employment['specialty_section_label'] = mean_employment.apply(
lambda row: f"{row['specialty_section']} ({row['total_graduates']:,} чел.)".replace(',', ' '), axis=1
)
# Построение графика
plt.figure(figsize=(16, 10))
plt.plot(mean_employment['specialty_section_label'], mean_employment['percent_employed'], marker='o', linestyle='-', color='g')
plt.xlabel('Укрупненная группа специальностей (Количество выпускников)')
plt.ylabel('Процент трудоустроенных')
plt.title('Процент трудоустройства по укрупненным группам специальностей')
# Настройка меток, сетки и поворота текста
plt.xticks(rotation=45, ha='right')
plt.ylim(0, mean_employment['percent_employed'].max() + 5)
plt.yticks(range(0, 101, 10))
plt.grid(visible=True, which='both', axis='both', linestyle='--', linewidth=0.5)
# Добавление значений процентов над каждой точкой
for i, percent in enumerate(mean_employment['percent_employed']):
plt.text(i, percent + 3, f'{percent:.1f}', ha='center', va='bottom', fontsize=8, color='black')
plt.tight_layout()
plt.margins(0)
plt.show()
plot_mean_employment_by_specialty_section()
In [12]:
def plot_employment_median_by_specialty():
query = """
SELECT s.specialty_name AS specialty, g.percent_employed, g.count_graduate
FROM Graduates g
JOIN Specialties s ON g.specialty_id = s.specialty_id
"""
df = fetch_data(query)
if df is not None:
# Расчет среднего процента трудоустроенных и общего числа выпускников по специальностям
mean_df = df.groupby('specialty').agg(
percent_employed=('percent_employed', 'mean'),
total_graduates=('count_graduate', 'sum')
).reset_index()
mean_df['employed_graduates'] = (mean_df['percent_employed'] * mean_df['total_graduates'] / 100).round()
# Сортировка по среднему проценту трудоустроенных
mean_df = mean_df.sort_values(by='percent_employed', ascending=False)
# Настройка графика
plt.figure(figsize=(17, len(mean_df) * 0.2))
# Построение столбчатой диаграммы
colors = ['blue' if specialty == 'Программная инженерия' else 'green' for specialty in mean_df['specialty']]
bars = plt.barh(mean_df['specialty'], mean_df['percent_employed'], color=colors)
plt.xlabel('Процент трудоустроенных')
plt.ylabel('Специальность')
plt.xlim(0, 100)
plt.title('Процент трудоустроенных по специальностям')
# Добавление текста с количеством трудоустроенных внутри столбца и процентом справа от столбца
for bar, percent, employed in zip(bars, mean_df['percent_employed'], mean_df['employed_graduates']):
# Количество трудоустроенных внутри столбца
plt.text(bar.get_width() / 2, bar.get_y() + bar.get_height() / 2,
f'{int(employed):,} чел.'.replace(',', ' '),
va='center', ha='center', color='white', fontsize=10)
# Процент трудоустроенных справа от столбца
plt.text(bar.get_width() + 1, bar.get_y() + bar.get_height() / 2,
f'{percent:.1f}%', va='center', ha='left', fontsize=10)
# Настройка осей
plt.gca().invert_yaxis()
plt.tight_layout()
plt.margins(0)
plt.show()
plot_employment_median_by_specialty()
In [13]:
def plot_employment_trends_by_education_level():
# Запрос данных с группировкой по уровню образования и году
query = """
SELECT g.education_level AS education_level, g.year AS year, AVG(g.percent_employed) AS avg_percent_employed
FROM Graduates g
WHERE g.year BETWEEN 2019 AND 2023
GROUP BY g.education_level, g.year
ORDER BY g.education_level, g.year;
"""
df = fetch_data(query)
if df is not None:
# Уникальные уровни образования для построения отдельных линий
education_levels = df['education_level'].unique()
plt.figure(figsize=(12, 8))
# Построение графика для каждого уровня образования
for level in education_levels:
level_data = df[df['education_level'] == level]
plt.plot(level_data['year'], level_data['avg_percent_employed'], marker='o', label=level)
# Настройки графика
plt.xlabel('Год')
plt.ylabel('Средний процент трудоустроенных')
plt.title('Динамика трудоустройства по уровням образования (2019-2023)')
plt.legend(title='Уровень образования')
plt.xticks(range(2019, 2024))
plt.ylim(0, 100)
plt.grid(visible=True, which='both', axis='both', linestyle='--', linewidth=0.5)
plt.tight_layout()
plt.show()
plot_employment_trends_by_education_level()
In [14]:
def plot_employment_trends_for_programming_engineering():
# Запрос для получения данных по "Программной инженерии"
query = """
SELECT g.year AS year, g.education_level AS education_level, AVG(g.percent_employed) AS avg_employment
FROM Graduates g
JOIN Specialties s ON g.specialty_id = s.specialty_id
WHERE s.specialty_name = 'Программная инженерия'
AND g.year BETWEEN 2019 AND 2023
GROUP BY g.year, g.education_level
ORDER BY g.year, g.education_level;
"""
# Получаем данные из базы данных
df = fetch_data(query)
if df is not None:
# Разделяем данные по уровням образования
df_bach_spec = df[df['education_level'] == 'Бакалавриат, специалитет']
df_masters = df[df['education_level'] == 'Магистратура']
# Создание графика
plt.figure(figsize=(10, 6))
# Линия для "Бакалавриат, специалитет"
plt.plot(df_bach_spec['year'], df_bach_spec['avg_employment'], marker='o', color='blue', label='Бакалавриат, специалитет')
# Линия для "Магистратура"
plt.plot(df_masters['year'], df_masters['avg_employment'], marker='o', color='green', label='Магистратура')
# Настройка графика
plt.xlabel('Год')
plt.ylabel('Средний процент трудоустроенных')
plt.title('Процент трудоустройства на специальности "Программная инженерия" (2019-2023)')
plt.ylim(0, 100)
plt.grid(visible=True, linestyle='--', linewidth=0.5)
# Добавление легенды
plt.legend(title="Уровень образования")
plt.xticks(range(2019, 2024))
# Подгонка макета и отображение графика
plt.tight_layout()
plt.show()
# Вызов функции для построения графика
plot_employment_trends_for_programming_engineering()
In [15]:
def plot_graduates_by_study_area():
query = """
SELECT sa.study_area_name, SUM(g.count_graduate) AS total_graduates
FROM Graduates g
JOIN Study_Areas sa ON g.study_area_id = sa.study_area_id
WHERE g.count_graduate IS NOT NULL
GROUP BY sa.study_area_name
ORDER BY total_graduates DESC;
"""
df = fetch_data(query)
if df is not None and not df.empty:
# Сортировка данных по количеству выпускников
df = df.sort_values(by="total_graduates", ascending=True)
# Построение графика
fig, ax = plt.subplots(figsize=(18, 8))
ax.plot(df["study_area_name"], df["total_graduates"], marker="o", linestyle="-", color="blue")
# Настройки осей и подписи
ax.set_xlabel("Укрупнённая группа специальностей (study_area_name)")
ax.set_ylabel("Количество выпускников")
ax.set_title("Количество выпускников по укрупнённым группам специальностей")
plt.xticks(rotation=45, ha="right")
plt.grid()
plt.tight_layout()
plt.show()
else:
print("Нет данных для построения графика.")
plot_graduates_by_study_area()
In [16]:
def plot_graduates_by_specialty_section():
query = """
SELECT s.specialty_section AS specialty_section, SUM(g.count_graduate) AS total_graduates
FROM Graduates g
JOIN Specialties s ON g.specialty_id = s.specialty_id
GROUP BY s.specialty_section
ORDER BY total_graduates DESC;
"""
df = fetch_data(query)
if df is not None:
# Сортировка данных по количеству выпускников
df = df.sort_values(by="total_graduates", ascending=True)
# Формируем метки для разделов специальностей с форматированием количества выпускников
df['specialty_section_label'] = df.apply(
lambda row: f"{row['specialty_section']} ({row['total_graduates']:,} чел.)".replace(',', ' '), axis=1
)
# Построение графика
plt.figure(figsize=(16, 10))
plt.plot(df['specialty_section_label'], df['total_graduates'], marker='o', linestyle='-', color='b')
plt.xlabel('Разделы специальностей (Количество выпускников)')
plt.ylabel('Количество выпускников')
plt.title('Количество выпускников по разделам специальностей')
# Настройка меток, сетки и поворота текста
plt.xticks(rotation=45, ha='right')
plt.grid(visible=True, which='both', axis='both', linestyle='--', linewidth=0.5)
plt.tight_layout()
plt.margins(0)
plt.show()
else:
print("Нет данных для построения графика.")
plot_graduates_by_specialty_section()
In [17]:
def plot_graduates_by_specialty():
query = """
SELECT s.specialty_name AS specialty, SUM(g.count_graduate) AS total_graduates
FROM Graduates g
JOIN Specialties s ON g.specialty_id = s.specialty_id
GROUP BY s.specialty_name
ORDER BY total_graduates DESC;
"""
df = fetch_data(query)
if df is not None:
# Сортировка данных по количеству выпускников в порядке убывания
df = df.sort_values(by="total_graduates", ascending=False)
# Настройка графика
plt.figure(figsize=(17, len(df) * 0.2))
# Построение горизонтальной столбчатой диаграммы
bars = plt.barh(df['specialty'], df['total_graduates'], color='green')
plt.xlabel('Количество выпускников')
plt.ylabel('Специальность')
plt.title('Количество выпускников по специальностям')
# Определяем максимальную ширину столбца
max_width = max(bar.get_width() for bar in bars)
# Добавление текста с количеством выпускников внутри столбца
for bar, total_graduates in zip(bars, df['total_graduates']):
plt.text(max_width / 2, # Текст всегда по центру самого длинного столбца
bar.get_y() + bar.get_height() / 2, # Центр по вертикали
f'{int(total_graduates):,} чел.'.replace(',', ' '),
va='center', ha='center', color='black', fontsize=10)
# Настройка осей
plt.gca().invert_yaxis()
plt.tight_layout()
plt.margins(0)
plt.show()
else:
print("Нет данных для построения графика.")
plot_graduates_by_specialty()
